﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class SubstituteSkuDA : ISubstituteSkuDA
    {
        private string CREATED_BY = "@CREATED_BY";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string OWNER_ID = "@OWNER_ID";
        private string PACK_ID = "@PACK_ID";
        private string QTY = "@QTY";
        private string SEQ_NO = "@SEQ_NO";
        private string SKU_ID = "@SKU_ID";
        private string SQL_CHECK_SUBSTITUTE_SKU_ID_UNIQUE = " SELECT COUNT(1) FROM @_@SUBSTITUTE_SKU WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND SUBSTITUTE_SKU_ID = @SUBSTITUTE_SKU_ID ";
        private string SQL_DELETE_ONE_SUBSTITUTE_SKU = " DELETE FROM @_@SUBSTITUTE_SKU WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND SUBSTITUTE_SKU_ID = @SUBSTITUTE_SKU_ID ";
        private string SQL_DELETE_SUBSTITUTE_SKU = " DELETE FROM @_@SUBSTITUTE_SKU WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID ";
        private string SQL_INSERT_SUBSTITUTE_SKU = " INSERT INTO @_@SUBSTITUTE_SKU ( WH_ID, OWNER_ID, SKU_ID, SUBSTITUTE_SKU_ID, SEQ_NO, PACK_ID, UOM_ID, UOM_QTY, QTY, SUB_PACK_ID, SUB_UOM, SUB_UOM_QTY, SUB_QTY, STATUS, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, IS_ACTIVE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @OWNER_ID, @SKU_ID, @SUBSTITUTE_SKU_ID, @SEQ_NO, @PACK_ID, @UOM_ID, @UOM_QTY, @QTY, @SUB_PACK_ID, @SUB_UOM, @SUB_UOM_QTY, @SUB_QTY, @STATUS, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @IS_ACTIVE, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_SUBSTITUTE_SKU = " SELECT  ROWID, WH_ID, OWNER_ID, SKU_ID, SUBSTITUTE_SKU_ID, SEQ_NO, PACK_ID, UOM_ID, UOM_QTY, QTY, SUB_PACK_ID, SUB_UOM, SUB_UOM_QTY, SUB_QTY, STATUS, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, IS_ACTIVE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE  FROM  @_@SUBSTITUTE_SKU ";
        private string SQL_UPDATE_SUBSTITUTE_SKU = " UPDATE @_@SUBSTITUTE_SKU SET WH_ID = @WH_ID, SKU_ID = @SKU_ID, SEQ_NO = @SEQ_NO, PACK_ID = @PACK_ID, UOM_ID = @UOM_ID, UOM_QTY = @UOM_QTY, QTY = @QTY, SUB_PACK_ID = @SUB_PACK_ID, SUB_UOM = @SUB_UOM, SUB_UOM_QTY = @SUB_UOM_QTY, SUB_QTY = @SUB_QTY, STATUS = @STATUS, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, IS_ACTIVE = @IS_ACTIVE, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND SUBSTITUTE_SKU_ID = @SUBSTITUTE_SKU_ID  ";
        private string STATUS = "@STATUS";
        private string SUB_PACK_ID = "@SUB_PACK_ID";
        private string SUB_QTY = "@SUB_QTY";
        private string SUB_UOM = "@SUB_UOM";
        private string SUB_UOM_QTY = "@SUB_UOM_QTY";
        private string SUBSTITUTE_SKU_ID = "@SUBSTITUTE_SKU_ID";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UOM_ID = "@UOM_ID";
        private string UOM_QTY = "@UOM_QTY";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";

        public bool CheckSubstituteSkuIDUnique(string ownerID, string skuID, string substituteSkuID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_ID", skuID), new SqlParameter("@SUBSTITUTE_SKU_ID", substituteSkuID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_SUBSTITUTE_SKU_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckSubstituteSkuIDUnique(DataBase dataBase, DbTransaction tran, string ownerID, string skuID, string substituteSkuID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_ID", skuID), new SqlParameter("@SUBSTITUTE_SKU_ID", substituteSkuID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_SUBSTITUTE_SKU_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteSubstituteSku(List<string> ownerID, List<string> skuID, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < ownerID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID[j]), new SqlParameter("@SKU_ID", skuID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_SUBSTITUTE_SKU.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteSubstituteSku(List<string> ownerID, List<string> skuID, List<string> substituteSkuID, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < ownerID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID[j]), new SqlParameter("@SKU_ID", skuID[j]), new SqlParameter("@SUBSTITUTE_SKU_ID", substituteSkuID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_ONE_SUBSTITUTE_SKU.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteSubstituteSku(DataBase dataBase, DbTransaction tran, List<string> ownerID, List<string> skuID, List<string> whLoginID)
        {
            int result = 0;
            for (int i = 0; i < whLoginID.Count; i++)
            {
                for (int j = 0; j < ownerID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID[j]), new SqlParameter("@SKU_ID", skuID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_SUBSTITUTE_SKU.Replace("@_@", whLoginID[i]), paras);
                }
            }
            return result;
        }

        public int DeleteSubstituteSku(DataBase dataBase, DbTransaction tran, List<string> ownerID, List<string> skuID, List<string> substituteSkuID, List<string> whLoginID)
        {
            int result = 0;
            for (int i = 0; i < whLoginID.Count; i++)
            {
                for (int j = 0; j < substituteSkuID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID[j]), new SqlParameter("@SKU_ID", skuID[j]), new SqlParameter("@SUBSTITUTE_SKU_ID", substituteSkuID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_ONE_SUBSTITUTE_SKU.Replace("@_@", whLoginID[i]), paras);
                }
            }
            return result;
        }

        public DataSet GetAllSubstituteSku(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_SUBSTITUTE_SKU.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllSubstituteSku(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_SUBSTITUTE_SKU.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public SubstituteSkuInfo GetSubstituteSkuByID(string ownerID, string skuID, string substituteSkuID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_SUBSTITUTE_SKU.Replace("@_@", whLoginID) + " WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND SUBSTITUTE_SKU_ID = @SUBSTITUTE_SKU_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_ID", skuID), new SqlParameter("@SUBSTITUTE_SKU_ID", substituteSkuID) };
            SubstituteSkuInfo substituteSkuInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    substituteSkuInfo = new SubstituteSkuInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["SUBSTITUTE_SKU_ID"].ToString(), (reader["SEQ_NO"].ToString() != "") ? int.Parse(reader["SEQ_NO"].ToString()) : 0, reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), (reader["UOM_QTY"].ToString() != "") ? decimal.Parse(reader["UOM_QTY"].ToString()) : 0M, (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["SUB_PACK_ID"].ToString(), reader["SUB_UOM"].ToString(), (reader["SUB_UOM_QTY"].ToString() != "") ? decimal.Parse(reader["SUB_UOM_QTY"].ToString()) : 0M, (reader["SUB_QTY"].ToString() != "") ? decimal.Parse(reader["SUB_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["IS_ACTIVE"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return substituteSkuInfo;
        }

        public SubstituteSkuInfo GetSubstituteSkuByID(DataBase dataBase, DbTransaction tran, string ownerID, string skuID, string substituteSkuID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_SUBSTITUTE_SKU.Replace("@_@", whLoginID) + " WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND SUBSTITUTE_SKU_ID = @SUBSTITUTE_SKU_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_ID", skuID), new SqlParameter("@SUBSTITUTE_SKU_ID", substituteSkuID) };
            SubstituteSkuInfo substituteSkuInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                substituteSkuInfo = new SubstituteSkuInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["SUBSTITUTE_SKU_ID"].ToString(), (reader["SEQ_NO"].ToString() != "") ? int.Parse(reader["SEQ_NO"].ToString()) : 0, reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), (reader["UOM_QTY"].ToString() != "") ? decimal.Parse(reader["UOM_QTY"].ToString()) : 0M, (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["SUB_PACK_ID"].ToString(), reader["SUB_UOM"].ToString(), (reader["SUB_UOM_QTY"].ToString() != "") ? decimal.Parse(reader["SUB_UOM_QTY"].ToString()) : 0M, (reader["SUB_QTY"].ToString() != "") ? decimal.Parse(reader["SUB_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["IS_ACTIVE"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return substituteSkuInfo;
        }

        public DataSet GetSubstituteSkuByQueryList(List<string> sqlWhere, SubstituteSkuQueryEntity substituteskuQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_SUBSTITUTE_SKU.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!substituteskuQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, substituteskuQuery.CurrentPage, substituteskuQuery.PageSize, substituteskuQuery.SortField, substituteskuQuery.SortDirection);
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetSubstituteSkuByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, SubstituteSkuQueryEntity substituteskuQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_SUBSTITUTE_SKU.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!substituteskuQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, substituteskuQuery.CurrentPage, substituteskuQuery.PageSize, substituteskuQuery.SortField, substituteskuQuery.SortDirection);
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public List<SubstituteSkuInfo> GetSubstituteSkuListByID(string ownerID, string skuID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_SUBSTITUTE_SKU.Replace("@_@", whLoginID) + " WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_ID", skuID) };
            List<SubstituteSkuInfo> substituteSkuInfo = new List<SubstituteSkuInfo>();
            SubstituteSkuInfo substituteSku = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    substituteSku = new SubstituteSkuInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["SUBSTITUTE_SKU_ID"].ToString(), (reader["SEQ_NO"].ToString() != "") ? int.Parse(reader["SEQ_NO"].ToString()) : 0, reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), (reader["UOM_QTY"].ToString() != "") ? decimal.Parse(reader["UOM_QTY"].ToString()) : 0M, (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["SUB_PACK_ID"].ToString(), reader["SUB_UOM"].ToString(), (reader["SUB_UOM_QTY"].ToString() != "") ? decimal.Parse(reader["SUB_UOM_QTY"].ToString()) : 0M, (reader["SUB_QTY"].ToString() != "") ? decimal.Parse(reader["SUB_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["IS_ACTIVE"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()));
                    substituteSkuInfo.Add(substituteSku);
                }
            }
            return substituteSkuInfo;
        }

        public List<SubstituteSkuInfo> GetSubstituteSkuListByID(DataBase dataBase, DbTransaction tran, string ownerID, string skuID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_SUBSTITUTE_SKU.Replace("@_@", whLoginID) + " WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_ID", skuID) };
            List<SubstituteSkuInfo> substituteSkuInfo = new List<SubstituteSkuInfo>();
            SubstituteSkuInfo substituteSku = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                substituteSku = new SubstituteSkuInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["SUBSTITUTE_SKU_ID"].ToString(), (reader["SEQ_NO"].ToString() != "") ? int.Parse(reader["SEQ_NO"].ToString()) : 0, reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), (reader["UOM_QTY"].ToString() != "") ? decimal.Parse(reader["UOM_QTY"].ToString()) : 0M, (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["SUB_PACK_ID"].ToString(), reader["SUB_UOM"].ToString(), (reader["SUB_UOM_QTY"].ToString() != "") ? decimal.Parse(reader["SUB_UOM_QTY"].ToString()) : 0M, (reader["SUB_QTY"].ToString() != "") ? decimal.Parse(reader["SUB_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["IS_ACTIVE"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()));
                substituteSkuInfo.Add(substituteSku);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return substituteSkuInfo;
        }

        public int InsertSubstituteSku(SubstituteSkuInfo substituteSkuInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    substituteSkuInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_SubstituteSku_Parameters(substituteSkuInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_SUBSTITUTE_SKU.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertSubstituteSku(DataBase dataBase, DbTransaction tran, SubstituteSkuInfo substituteSkuInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    substituteSkuInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_SubstituteSku_Parameters(substituteSkuInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_SUBSTITUTE_SKU.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_SubstituteSku_Parameters(SubstituteSkuInfo substituteSkuInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.SUBSTITUTE_SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.SEQ_NO, SqlDbType.Int, 4), new SqlParameter(this.PACK_ID, SqlDbType.VarChar, 20), new SqlParameter(this.UOM_ID, SqlDbType.VarChar, 20), new SqlParameter(this.UOM_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.QTY, SqlDbType.Decimal, 13), new SqlParameter(this.SUB_PACK_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SUB_UOM, SqlDbType.VarChar, 20), new SqlParameter(this.SUB_UOM_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.SUB_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.STATUS, SqlDbType.VarChar, 1), new SqlParameter(this.UDF1, SqlDbType.VarChar, 20), new SqlParameter(this.UDF2, SqlDbType.VarChar, 20), 
                new SqlParameter(this.UDF3, SqlDbType.VarChar, 20), new SqlParameter(this.UDF4, SqlDbType.VarChar, 20), new SqlParameter(this.UDF5, SqlDbType.VarChar, 20), new SqlParameter(this.UDF6, SqlDbType.VarChar, 20), new SqlParameter(this.UDF7, SqlDbType.VarChar, 20), new SqlParameter(this.UDF8, SqlDbType.VarChar, 20), new SqlParameter(this.UDF9, SqlDbType.VarChar, 20), new SqlParameter(this.UDF10, SqlDbType.VarChar, 20), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(substituteSkuInfo.WhID))
            {
                paramArray[0].Value = substituteSkuInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = substituteSkuInfo.OwnerID;
            paramArray[2].Value = substituteSkuInfo.SkuID;
            paramArray[3].Value = substituteSkuInfo.SubstituteSkuID;
            paramArray[4].Value = substituteSkuInfo.SeqNo;
            if (!string.IsNullOrEmpty(substituteSkuInfo.PackID))
            {
                paramArray[5].Value = substituteSkuInfo.PackID;
            }
            else
            {
                paramArray[5].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(substituteSkuInfo.UomID))
            {
                paramArray[6].Value = substituteSkuInfo.UomID;
            }
            else
            {
                paramArray[6].Value = DBNull.Value;
            }
            paramArray[7].Value = substituteSkuInfo.UomQty;
            paramArray[8].Value = substituteSkuInfo.Qty;
            if (!string.IsNullOrEmpty(substituteSkuInfo.SubPackID))
            {
                paramArray[9].Value = substituteSkuInfo.SubPackID;
            }
            else
            {
                paramArray[9].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(substituteSkuInfo.SubUom))
            {
                paramArray[10].Value = substituteSkuInfo.SubUom;
            }
            else
            {
                paramArray[10].Value = DBNull.Value;
            }
            paramArray[11].Value = substituteSkuInfo.SubUomQty;
            paramArray[12].Value = substituteSkuInfo.SubQty;
            if (!string.IsNullOrEmpty(substituteSkuInfo.Status))
            {
                paramArray[13].Value = substituteSkuInfo.Status;
            }
            else
            {
                paramArray[13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(substituteSkuInfo.Udf1))
            {
                paramArray[14].Value = substituteSkuInfo.Udf1;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(substituteSkuInfo.Udf2))
            {
                paramArray[15].Value = substituteSkuInfo.Udf2;
            }
            else
            {
                paramArray[15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(substituteSkuInfo.Udf3))
            {
                paramArray[0x10].Value = substituteSkuInfo.Udf3;
            }
            else
            {
                paramArray[0x10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(substituteSkuInfo.Udf4))
            {
                paramArray[0x11].Value = substituteSkuInfo.Udf4;
            }
            else
            {
                paramArray[0x11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(substituteSkuInfo.Udf5))
            {
                paramArray[0x12].Value = substituteSkuInfo.Udf5;
            }
            else
            {
                paramArray[0x12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(substituteSkuInfo.Udf6))
            {
                paramArray[0x13].Value = substituteSkuInfo.Udf6;
            }
            else
            {
                paramArray[0x13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(substituteSkuInfo.Udf7))
            {
                paramArray[20].Value = substituteSkuInfo.Udf7;
            }
            else
            {
                paramArray[20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(substituteSkuInfo.Udf8))
            {
                paramArray[0x15].Value = substituteSkuInfo.Udf8;
            }
            else
            {
                paramArray[0x15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(substituteSkuInfo.Udf9))
            {
                paramArray[0x16].Value = substituteSkuInfo.Udf9;
            }
            else
            {
                paramArray[0x16].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(substituteSkuInfo.Udf10))
            {
                paramArray[0x17].Value = substituteSkuInfo.Udf10;
            }
            else
            {
                paramArray[0x17].Value = DBNull.Value;
            }
            paramArray[0x18].Value = substituteSkuInfo.IsActive;
            paramArray[0x19].Value = substituteSkuInfo.CreatedBy;
            paramArray[0x1a].Value = substituteSkuInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateSubstituteSku(SubstituteSkuInfo substituteSkuInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    substituteSkuInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_SubstituteSku_Parameters(substituteSkuInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_SUBSTITUTE_SKU.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateSubstituteSku(DataBase dataBase, DbTransaction tran, SubstituteSkuInfo substituteSkuInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    substituteSkuInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_SubstituteSku_Parameters(substituteSkuInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_SUBSTITUTE_SKU.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

